package com.scalar.chiptrack.adaptor.lingsen;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.ShippedLot;
import com.scalar.chiptrack.adaptor.AssemblyLot;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.dao.InventoryLotDAO;
import com.scalar.chiptrack.adaptor.dao.ShippedLotDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.adaptor.dao.AssemblyAdaptorDAO;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.ScalarException;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Date;


public class LingsenDataParser extends JobHandler implements AdaptorConstants
{
    private HashMap<String, ShippedLot> m_sssyshipDataMap  = new HashMap<String, ShippedLot>();
    private HashMap<String, AssemblyLot> m_assemblyDataMap = new HashMap<String, AssemblyLot>();
    private HashMap<String, InventoryLot> m_dieBankDataMap = new HashMap<String, InventoryLot>();

    private Logger m_parserLogger       = null;
    private String m_dataOutputDir      = null;

    public  LingsenDataParser() throws ScalarException
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir( VENDOR_NAME_LINGSEN , WIP_TYPE_ASSEMBLY );

            if ( m_dataOutputDir != null )
            {
                AdaptorUtils.makeAllAdaptorDirs( m_dataOutputDir );

                m_parserLogger= AdaptorUtils.getDataParserLogger( m_dataOutputDir,VENDOR_NAME_LINGSEN );

                AdaptorUtils.setLogger( m_parserLogger );
                ShippedLotDAO.setLogger( m_parserLogger);
                InventoryLotDAO.setLogger(m_parserLogger);
                AssemblyAdaptorDAO.setLogger( m_parserLogger );
            }
        }
        catch ( Exception se )
        {
            log( "ERROR!! ERROR!! ERROR!! Exception while loading LINGSEN output directory details from database, Exception: " + se );
            System.out.println( "ERROR!! ERROR!! ERROR!! Exception while loading LINGSEN output directory details from database, Exception: " + se );
        }
    }
    public static void main( String args[] ) throws Exception
    {
        try
        {
            new LingsenDataParser().execute();
        }
        catch ( Exception e )
        {
            throw e;
        }
    }
    public void execute() throws Exception
    {
        AdaptorUtils.printHeader( VENDOR_NAME_LINGSEN, CONSTANT_PARSER );

        File doneDestDir = new File( m_dataOutputDir + "/done" );
        File dataoutDir  = new File( m_dataOutputDir );

        File[] fileList = dataoutDir.listFiles();

        if ( ( fileList == null ) || (fileList.length <= 3))
        {
            log("No file found in directory: " + dataoutDir);
            System.out.println("No file found in directory: " + dataoutDir );
            throw new ScalarException("No file found in directory: " + dataoutDir+ "----\n\n" );
        }

        ArrayList<File> successFileList = new ArrayList<File>();

        boolean success;

        for ( int i = 0; i < fileList.length; i++ )
        {
            if ( fileList[i].isDirectory() )
            {
                continue;
            }

            try
            {
                if ( ( fileList[i].getName().startsWith( LingsenAdaptor.FILE_NAME_WIP )  || fileList[i].getName().indexOf( LingsenAdaptor.FILE_NAME_WIP )  != -1 ) && !fileList[i].getName().startsWith("MSLINV") )
                {
                     parseAssemblyWipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }

                success = true;
            }
            catch (Exception e)
            {
                success = false;
                log("Exception while parsing file: " + fileList[i].getAbsolutePath() + "\nException: " + e);
                e.printStackTrace();
                throw e;
            }
            try
            {
                if ( fileList[i].getName().startsWith( LingsenAdaptor.FILE_NAME_DIEBANK )  || fileList[i].getName().indexOf( LingsenAdaptor.FILE_NAME_DIEBANK )  != -1)
                {
                     parseDieBankReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                }

                success = true;
            }
            catch (Exception e)
            {
                success = false;
                log("Exception while parsing file: " + fileList[i].getAbsolutePath() + "\nException: " + e);
                e.printStackTrace();
                throw e;
            }
        }

        /*Updating the Die Bank Lot Information*/
        try
        {
            // Save Die Bank Information
            if (m_dieBankDataMap.isEmpty() == false)
            {
                log(" Updating Die Bank Lot Information");
                InventoryLotDAO.saveInventoryLot(null, m_dieBankDataMap.values().iterator(), LOCATION_TYPE_DIE_BANK, VENDOR_NAME_LINGSEN);
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_DIE_BANK, VENDOR_NAME_LINGSEN);
            }
            success = true;
            m_dieBankDataMap.clear();
            m_dieBankDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Die Bank Lot information into database: " + se);
            throw se;
        }

        try
        {
            // Save Assembly Information
            if ( m_assemblyDataMap.isEmpty() == false )
            {
                log( " Updating Assembly Lot Information" );
                AssemblyAdaptorDAO.saveAssemblyLot( null, m_assemblyDataMap.values().iterator(), WIP_TYPE_ASSEMBLY, VENDOR_NAME_LINGSEN );
            }
            else
            {
                AssemblyAdaptorDAO.closeAssemblyLot( null, WIP_TYPE_ASSEMBLY, VENDOR_NAME_LINGSEN);
            }
            success = true;
            m_assemblyDataMap.clear();
            m_assemblyDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log( "ERROR!! ERROR!! ERROR!! Could not update Assembly Lot information into database: " + se );
            throw se;
        }

        // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
        AdaptorDAO.sendAllNotifications( VENDOR_NAME_LINGSEN,true, true, true );

        // To Close the Drop Out Lots
        AdaptorDAO.closeDropOutLots( VENDOR_NAME_LINGSEN );

        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( LingsenAdaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);
        // transaction 9
        try
        {
            if (success)
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, LingsenAdaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println("SUCCESSFULLY UPDATED IN THE DATABASE");
            }
        }
        catch (ScalarException se)
        {
            success = false;  //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, LingsenAdaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log("WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se);
            throw se;
        }
       
        // Step 10
        // Move the successful files to 'done' directory
        if (success)
        {
            AdaptorUtils.moveFilesToDone( successFileList, doneDestDir);
        }
    }

    private void parseAssemblyWipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result     = null;

        AssemblyLot assemblyLot = null;
        String partNumber       = null;
        String fabLotNumber     = null;
        String lotNumber        = null;


        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);

            log( "\nFound " + fileName );
            log( "\nParsing the file for Assembly Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for Assembly Wip Report..." );

            result = parser.parse();

            while ( result.next() )
            {                   
                partNumber      = result.getString("DeviceNo");
                fabLotNumber    = result.getString("CusLotNo");
                lotNumber = result.getString("RunNo");
                if ( lotNumber == null )
                {
                    lotNumber = fabLotNumber;

                }

                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                    if (  result.getString("IQC") != null && !"0".equalsIgnoreCase(result.getString("IQC") )
                            || result.getString("W/S") != null && !"0".equalsIgnoreCase( result.getString("W/S") )
                            || result.getString("D/A") != null && !"0".equalsIgnoreCase( result.getString("D/A") )
                            || result.getString("W/B") != null && !"0".equalsIgnoreCase( result.getString("W/B") )
                            || result.getString("3Q") != null && !"0".equalsIgnoreCase( result.getString("3Q") )
                            || result.getString("COAT") != null && !"0".equalsIgnoreCase( result.getString("COAT") )
                            || result.getString("MOLD") != null && !"0".equalsIgnoreCase( result.getString("MOLD") )
                            || result.getString("B/M") != null && !"0".equalsIgnoreCase( result.getString("B/M") )
                            || result.getString("CURE") != null && !"0".equalsIgnoreCase( result.getString("CURE") )
                            || result.getString("DF2") != null && !"0".equalsIgnoreCase( result.getString("DF2") )
                            || result.getString("DF1") != null && !"0".equalsIgnoreCase(result.getString("DF1"))
                            || result.getString("S/PLAT") != null && !"0".equalsIgnoreCase(result.getString("S/PLAT"))
                            || result.getString("T/M") != null && !"0".equalsIgnoreCase(result.getString("T/M"))
                            || result.getString("PF") != null && !"0".equalsIgnoreCase(result.getString("PF"))
                            || result.getString("T/F") != null && !"0".equalsIgnoreCase(result.getString("T/F"))
                            || result.getString("SCAN") != null && !"0".equalsIgnoreCase(result.getString("SCAN"))
                            || result.getString("F/V") != null && !"0".equalsIgnoreCase(result.getString("F/V"))
                            || result.getString("OQA") != null && !"0".equalsIgnoreCase(result.getString("OQA")) 
                            || result.getString("80 TS") != null && !"0".equalsIgnoreCase(result.getString("80 TS"))
                            || result.getString("FG") != null && !"0".equalsIgnoreCase(result.getString("FG")) )


                   {
                        log( "In Assembly" + lotNumber ) ;

                        assemblyLot = m_assemblyDataMap.get( partNumber + lotNumber + fabLotNumber );
                        if (assemblyLot == null)
                        {
                            assemblyLot = new AssemblyLot(VENDOR_NAME_LINGSEN );
                            m_assemblyDataMap.put( partNumber + lotNumber + fabLotNumber, assemblyLot);
                        }

                        int quantity =0;

                        // FOR SETTING THE QTY IN
                        if( result.getString("IQC") != null && !"0".equalsIgnoreCase(result.getString("IQC") ) )
                        {
                            if ( result.getString("IQC").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("IQC").substring(0, result.getString("IQC").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("IQC") );
                            }

                           assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", IQC") ;
                            }
                            else
                            {
                                assemblyLot.setStage("IQC");
                            }
                        }
                        if(result.getString("W/S") != null && !"0".equalsIgnoreCase( result.getString("W/S") ) )
                        {
                            if ( result.getString("W/S").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("W/S").substring(0, result.getString("W/S").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("W/S") );
                            }
                           assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Wafer Saw") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Wafer Saw");
                            }
                        }
                        if(result.getString("D/A") != null && !"0".equalsIgnoreCase(result.getString("D/A")) )
                        {
                            if ( result.getString("D/A").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("D/A").substring(0, result.getString("D/A").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("D/A") );
                            }
                           assemblyLot =  m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);

                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() )  + quantity  ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Die Attach") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Die Attach");
                            }
                        }

                        if( result.getString("W/B") != null && !"0".equalsIgnoreCase( result.getString("W/B") ) )
                        {
                           if ( result.getString("W/B").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("W/B").substring(0, result.getString("W/B").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("W/B") );
                            }
                           assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Wire Bond") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Wire Bond");
                            }
                        }

                        if( result.getString("3Q") != null && !"0".equalsIgnoreCase( result.getString("3Q") ) )
                        {
                            if ( result.getString("3Q").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("3Q").substring(0, result.getString("3Q").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("3Q") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", 3rd Optical") ;
                            }
                            else
                            {
                                assemblyLot.setStage("3rd Optical");
                            }
                        }

                        if( result.getString("COAT") != null && !"0".equalsIgnoreCase( result.getString("COAT") ) )
                        {
                            if ( result.getString("COAT").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("COAT").substring(0, result.getString("COAT").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("COAT") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Coating") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Coating");
                            }
                        }
                        if( result.getString("MOLD") != null && !"0".equalsIgnoreCase(result.getString("MOLD") ) )
                        {
                            if ( result.getString("MOLD").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("MOLD").substring(0, result.getString("MOLD").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("MOLD") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Molding") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Molding");
                            }
                        }
                       if( result.getString("B/M") != null && !"0".equalsIgnoreCase( result.getString("B/M") ) )
                        {
                            if ( result.getString("B/M").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("B/M").substring(0, result.getString("B/M").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("B/M") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Back Marking") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Back Marking");
                            }
                        }
                        if( result.getString("CURE") != null && !"0".equalsIgnoreCase( result.getString("CURE") ) )
                        {
                            if ( result.getString("CURE").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("CURE").substring(0, result.getString("CURE").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("CURE") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", PostMoldCure") ;
                            }
                            else
                            {
                                assemblyLot.setStage("PostMoldCure");
                            }
                        }
                       if( result.getString("DF2") != null && !"0".equalsIgnoreCase( result.getString("DF2") ) )
                        {
                            if ( result.getString("DF2").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("DF2").substring(0, result.getString("DF2").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("DF2") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", De-Flash") ;
                            }
                            else
                            {
                                assemblyLot.setStage("De-Flash");
                            }
                        }
                       if( result.getString("DF1") != null && !"0".equalsIgnoreCase( result.getString("DF1") ) )
                        {
                            if ( result.getString("DF1").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("DF1").substring(0, result.getString("DF1").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("DF1") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", De-Junk") ;
                            }
                            else
                            {
                                assemblyLot.setStage("De-Junk");
                            }
                        }
                       if( result.getString("S/PLAT") != null && !"0".equalsIgnoreCase( result.getString("S/PLAT") ) )
                        {
                            if ( result.getString("S/PLAT").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("S/PLAT").substring(0, result.getString("S/PLAT").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("S/PLAT") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Plating") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Plating");
                            }
                        }
                       if( result.getString("T/M") != null && !"0".equalsIgnoreCase( result.getString("T/M") ) )
                        {
                            if ( result.getString("T/M").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("T/M").substring(0, result.getString("T/M").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("T/M") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Top Marking") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Top Marking");
                            }
                        }
                       if( result.getString("PF") != null && !"0".equalsIgnoreCase( result.getString("PF") ) )
                        {
                            if ( result.getString("PF").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("PF").substring(0, result.getString("PF").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("PF") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", PerForm") ;
                            }
                            else
                            {
                                assemblyLot.setStage("PerForm");
                            }
                        }
                       if( result.getString("T/F") != null && !"0".equalsIgnoreCase( result.getString("T/F") ) )
                        {
                            if ( result.getString("T/F").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("T/F").substring(0, result.getString("T/F").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("T/F") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Trim Form") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Trim Form");
                            }
                        }
                        if( result.getString("SCAN") != null && !"0".equalsIgnoreCase( result.getString("SCAN") ) )
                        {
                            if ( result.getString("SCAN").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("SCAN").substring(0, result.getString("SCAN").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("SCAN") );
                            }
                           assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                           if( assemblyLot.getQtyIn() != null )
                           {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Lead Scan") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Lead Scan");
                            }
                        }

                        if( result.getString("F/V") != null && !"0".equalsIgnoreCase( result.getString("F/V") ) )
                        {
                            if ( result.getString("F/V").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("F/V").substring(0, result.getString("F/V").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("F/V") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                            if( assemblyLot.getQtyIn() != null )
                            {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Final Visual") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Final Visul");
                            }
                        }
                        if( result.getString("OQA") != null && !"0".equalsIgnoreCase( result.getString("OQA") ) )
                        {
                            if ( result.getString("OQA").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("OQA").substring(0, result.getString("OQA").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("OQA") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                            if( assemblyLot.getQtyIn() != null )
                            {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", OQA") ;
                            }
                            else
                            {
                                assemblyLot.setStage("OQA");
                            }
                        }
                        if( result.getString("80 TS") != null && !"0".equalsIgnoreCase( result.getString("80 TS") ) )
                        {
                            if ( result.getString("80 TS").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("80 TS").substring(0, result.getString("80 TS").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("80 TS") );
                            }
                            assemblyLot = m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                            if( assemblyLot.getQtyIn() != null )
                            {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", Final Testing") ;
                            }
                            else
                            {
                                assemblyLot.setStage("Final Testing");
                            }
                        }
                        if( result.getString("FG") != null && !"0".equalsIgnoreCase( result.getString("FG") ) )
                        {
                            if ( result.getString("FG").indexOf(".") != -1 )
                            {
                                quantity = Integer.parseInt( result.getString("FG").substring(0, result.getString("FG").indexOf(".") ) );
                            }
                            else
                            {
                                 quantity = Integer.parseInt( result.getString("FG") );
                            }
                            assemblyLot =  m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);
                            if( assemblyLot.getQtyIn() != null )
                            {
                                try
                                {
                                    assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + quantity ) + "" );
                                }
                                catch(Exception e)
                                {
                                    assemblyLot.setQtyIn( quantity+"" );
                                }
                            }
                            else
                            {
                                assemblyLot.setQtyIn( quantity+"" );
                            }
                            if ( assemblyLot.getStage() != null )
                            {
                                assemblyLot.setStage ( assemblyLot.getStage() +", FG") ;
                            }
                            else
                            {
                                assemblyLot.setStage("FG");
                            }
                        }

                        assemblyLot.setLotNumber( lotNumber );
                        assemblyLot.setDeviceName( partNumber );
                       
                        if ( result.getString("HD") != null && result.getString("HD").trim().length() > 0 )
                        {
                           assemblyLot.setStatus(LOT_STATUS_HOLD);
                           assemblyLot.setHoldReason( result.getString("HD") );
                        }
                        else
                        {
                           assemblyLot.setStatus(LOT_STATUS_RUNNING);
                        }
                        assemblyLot.setFabLotNumber( fabLotNumber );
                        assemblyLot.setPackageType( result.getString("Package") );
                        assemblyLot.setPONumber( result.getString("PO"));
                        if ( "E".equalsIgnoreCase( result.getString("LotType") ) )
                        {
                            assemblyLot.setCategory( LOT_CATEGORY_TYPE_ENGG );
                        }
                        else
                        {
                            assemblyLot.setCategory( LOT_CATEGORY_TYPE_PROD );
                        }


                        if ( result.getString("InDate") != null )
                        {
                            assemblyLot.setDateIn( StringUtils.formatDate( result.getString( "InDate"), "yy/MM/dd", "MM/dd/yyyy" ));
                        }

                        if (result.getString("ESOD") != null )
                        {
                            assemblyLot.setExpectedDateOut( StringUtils.formatDate(result.getString("ESOD"), "yy/MM/dd", "MM/dd/yyyy" ) );
                        }
                        else if ( result.getString("1st Shipping Date") != null )
                        {
                             assemblyLot.setExpectedDateOut( StringUtils.formatDate(result.getString("1st Shipping Date"), "yy/MM/dd", "MM/dd/yyyy" ) );
                        }
                        assemblyLot.setProcessType( WIP_TYPE_ASSEMBLY );
                    }

                }

            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            throw e;
        }
    }

    private void parseDieBankReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result     = null;

        InventoryLot inventoryLot = null;
        String partNumber       = null;
        String fabLotNumber     = null;


        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);

            log( "\nFound " + fileName );
            log( "\nParsing the file for DieBank Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for DieBank Wip Report..." );

            result = parser.parse();

            while ( result.next() )
            {
                partNumber      = result.getString("Device");
                fabLotNumber    = result.getString("LotNo");

                if ( partNumber != null && partNumber.trim().length() > 0 && fabLotNumber != null && fabLotNumber.trim().length() > 0 )
                {
                    inventoryLot = (InventoryLot)m_dieBankDataMap.get( partNumber + fabLotNumber );

                    if ( inventoryLot == null )
                    {
                        inventoryLot = new InventoryLot( VENDOR_NAME_LINGSEN, LOCATION_TYPE_DIE_BANK );
                        m_dieBankDataMap.put( partNumber + fabLotNumber, inventoryLot );
                    }
                    if (inventoryLot.getQtyIn() != null)
                    {
                        try
                        {
                            inventoryLot.setQtyIn((Integer.parseInt(inventoryLot.getQtyIn()) + Integer.parseInt(result.getString("EOHDieQty"))) + "");
                            if ( inventoryLot.getWaferQtyIn() != null )
                            {
                                inventoryLot.setWaferQtyIn((Integer.parseInt(inventoryLot.getWaferQtyIn()) + Integer.parseInt(result.getString("EOHWaferQty"))) + "");
                            }
                        }
                        catch (Exception e)
                        {
                            inventoryLot.setQtyIn(result.getString("EOHDieQty"));
                            inventoryLot.setWaferQtyIn( result.getString("EOHWaferQty") );
                        }
                    }
                    else
                    {
                        inventoryLot.setQtyIn(result.getInt("EOHDieQty") +"");
                        inventoryLot.setWaferQtyIn( result.getInt("EOHWaferQty")+"");
                    }


                    if (inventoryLot.getQtyIn() != null && !"0".equalsIgnoreCase( inventoryLot.getQtyIn() ) )
                    {
                        inventoryLot.setPartNumber( partNumber );
                        inventoryLot.setLotNumber( fabLotNumber );
                        inventoryLot.setFabLotNumber( fabLotNumber );
                        if ( result.getString("Rcv_date") != null )
                        {
                            inventoryLot.setDateIn( StringUtils.formatDate(result.getString("Rcv_date"), "yyyy/MM/dd","MM/dd/yyyy" ) );
                        }
                        inventoryLot.setStatus( LOT_STATUS_RUNNING );
                        inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                        
                        if ("*".equalsIgnoreCase( result.getString("WaferNo") ) )
                        {
                            inventoryLot.setWaferId( "0");
                        }
                        else
                        {
                            inventoryLot.setWaferId( result.getString("WaferNo").replaceAll("\\*", ""));
                        }
                    }
                    
                }
            }
       }
       catch(Exception e )
       {
            e.printStackTrace();
            throw e;
       }
    }

    private void log( String message )
    {
      if ( message == null ) return;

      if ( m_parserLogger != null )
      {
          m_parserLogger.logDebug( message + LINE_SEPARATOR );
      }
      else
      {
          System.out.println( message );
      }
    } // end of log method

}
